# -*- coding: utf-8 -*-

"""
@Datetime: 2019/1/31
@Author: Zhang Yafei
"""
import sqlite3
import os

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

DB_DIR = os.path.join(BASE_DIR, 'db.sqlite3')


class SqliteDB(object):
    def __init__(self):
        self.conn = sqlite3.connect(DB_DIR)  # db不存在时将自动创建db
        self.cursor = self.conn.cursor()

    def close(self):
        self.cursor.close()
        self.conn.close()

    def execute(self, sql, params=tuple()):
        self.cursor.execute(sql, params)
        self.close()

    def fetchone(self, sql, params=tuple()):
        result = self.cursor.execute(sql, params)
        data = result.fetchone()
        self.close()
        return data

    def fetchall(self, sql, params=tuple()):
        results = self.cursor.execute(sql, params)
        data = results.fetchall()
        self.close()
        return data


if __name__ == '__main__':
    sqlite = SqliteDB()
    # 1. 建表
    # sql = '''create table happy(
    #          username text,
    #          password text,
    #          id int)'''
    # sqlite.execute(sql)

    # 2. 插入数据
    # sqlite.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
    #       VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")

    # 3. 更改数据
    # sqlite.execute("UPDATE COMPANY SET  ID=99  WHERE ID=2")

    # 4. 删除表里面的数据
    # c.execute("DELETE FROM COMPANY WHERE ID=4")
    # c.execute("DELETE FROM COMPANY WHERE ID=3")

    # 5. 查询
    data = sqlite.fetchall('select * from label limit 1')
    print(data)
    # 输出
    '''
    [('盘龙云海(排毒养颜胶囊)', 509881, '广东深圳龙岗区/女', '昨天吃的，今天就拉肚子了。感觉肚子有点涨痛！不知道效果怎么样~~~~~',
      '昨天/吃/的/，/今天/就/拉肚子/SB了/。/感觉/肚子/PB有点/涨痛/SB！/不/知道/效果/怎么样/~/~/~/~/~', '2011-09-30 15:26:00',
      'http://ypk.39.net/509881/comment/k0_p...', '昨天/吃/的/，/今天/就/拉肚子/SB了/。/感觉/肚子/PB有点/涨痛/SB！/不/知道/效果/怎么样/~/~/~/~/~',
      '昨天/吃/的/，/今天/就/拉肚子/SB了/。/感觉/肚子/PB有点/涨痛/SB！/不/知道/效果/怎么样/~/~/~/~/~')]
    '''
